from tools import *
import yaml
import pandas as pd
from openpyxl.comments import Comment
from openpyxl.styles import PatternFill
from openpyxl.utils import get_column_letter

rules = {}  # 质控规则
column_map = {}  # 获取列标题映射（列名 -> 列字母）
handle_num = 0  # 单个表格处理数据总数


# 初始化函数------------------------------------------------------------------------------------------
# 加载质控规则
def get_rules():
    global rules
    with open('rules.yaml', 'r', encoding='utf-8') as file:
        rules = yaml.safe_load(file)
        print('质控规则加载完毕......')


# 初始化数据
def initial_data(df):
    """

    :param df: 预处理表格
    :return:
    """
    global column_map
    global handle_num
    # 并获取操作数
    handle_num = len(df)
    print(f'总操作数：{handle_num}')
    # 获取列标题映射（列名 -> 列字母）
    column_map = {col: get_column_letter(idx + 1) for idx, col in enumerate(df.columns)}
    # print(column_map)
    print('数据初始化完毕......')


# 功能函数------------------------------------------------------------------------------------------
# 随访分类
def sffl(df, ws, col_name1, col_name2, col_name3, col_name4, col_name5, col_name6, col_name7, col_name8, error_list):
    """

    :param df: 预处理表格
    :param ws: 待生成表格
    :param col_name1: 随访类型
    :param col_name2: 此次随访分类
    :param col_name3: 血压随访分类
    :param col_name4: 收缩压
    :param col_name5: 舒张压
    :param col_name6: 出生日期
    :param col_name7: 血糖随访分类
    :param col_name8: 空腹血糖

    :param error_list: 报错列表
    :return:
    """
    color = str(rules[col_name1]['color'])
    # 设置单元格背景色
    fill = PatternFill(
        start_color=color,
        end_color=color,
        fill_type='solid'
    )
    col_letter0 = column_map[col_name1]  # 随访类型
    col_letter = column_map[col_name2]  # 此次随访分类
    col_letter1 = column_map[col_name3]  # 血压随访分类
    col_letter2 = column_map[col_name7]  # 血糖随访分类
    # print(col_letter)
    old_age = rules[col_name1]['age']
    xt_max = rules[col_name1]['xt']
    xy_val = rules[col_name1]['val1']
    xt_val = rules[col_name1]['val2']
    xy_and_xt_val = rules[col_name1]['val3']
    val = rules[col_name1]['value']  # 控制满意
    n_old_ssy = rules[col_name1]['非老年人'][col_name4]
    n_old_szy = rules[col_name1]['非老年人'][col_name5]
    y_old_ssy = rules[col_name1]['老年人'][col_name4]
    y_old_szy = rules[col_name1]['老年人'][col_name5]
    gap_com = rules[col_name1]['comment']
    n_old_com = rules[col_name1]['非老年人']['comment']
    y_old_com = rules[col_name1]['老年人']['comment']
    xt_com = rules[col_name1]['血糖']['comment']
    for row_idx in range(handle_num):
        excel_row = row_idx + 2  # Excel数据行号（标题在第1行）
        cell_value = df.at[row_idx, col_name1]  # 随访类型
        y_old_flag = False  # 标志，用来方便的处理随访分类都有的情况
        n_old_flag = False
        xt_flag = False
        try:
            if pd.isna(cell_value):
                cell = ws[f'{col_letter0}{excel_row}']
                cell.fill = fill  # 标色
                comment = Comment(gap_com, '')
                cell.comment = comment
        except Exception as e:
            err_info = f"处理单元格 {col_letter0}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)
        try:
            if not pd.isna(cell_value) and cell_value == xy_val:
                cell_value2 = df.at[row_idx, col_name2]  # 此次随访分类
                cell_value6 = df.at[row_idx, col_name6]  # 出生日期
                ssy_ = 1000
                szy_ = 1000
                # 出生日期不明，不进行处理
                if not pd.isna(cell_value6):
                    birth_year = int(str(cell_value6)[:4])
                    current_year = datetime.now().year
                    age = current_year - birth_year
                    cell_value4 = df.at[row_idx, col_name4]  # 收缩压
                    cell_value5 = df.at[row_idx, col_name5]  # 舒张压
                    ssy_ = float(cell_value4) if not pd.isna(cell_value4) else ssy_
                    szy_ = float(cell_value5) if not pd.isna(cell_value5) else szy_
                    if age < old_age:  # 非老年人
                        if ssy_ < n_old_ssy and szy_ < n_old_szy:
                            if pd.isna(cell_value2) or val not in cell_value2:
                                n_old_flag = True
                                cell = ws[f'{col_letter}{excel_row}']
                                cell.fill = fill  # 标色
                                comment = Comment(n_old_com, '')
                                cell.comment = comment
                    else:  # 老年人
                        if ssy_ < y_old_ssy and szy_ < y_old_szy:
                            if pd.isna(cell_value2) or val not in cell_value2:
                                y_old_flag = True
                                cell = ws[f'{col_letter}{excel_row}']
                                cell.fill = fill  # 标色
                                comment = Comment(y_old_com, '')
                                cell.comment = comment
        except Exception as e:
            err_info = f"处理单元格 {col_letter}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)
        try:
            if not pd.isna(cell_value) and cell_value == xt_val:
                cell_value2 = df.at[row_idx, col_name2]  # 此次随访分类
                cell_value8 = df.at[row_idx, col_name8]  # 空腹血糖
                if not pd.isna(cell_value8) and str(cell_value8) != '未查' and float(cell_value8) < xt_max:
                    if pd.isna(cell_value2) or val not in cell_value2:
                        cell = ws[f'{col_letter}{excel_row}']
                        cell.fill = fill  # 标色
                        comment = Comment(xt_com, '')
                        cell.comment = comment
        except Exception as e:
            err_info = f"处理单元格 {col_letter}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)
        cell_value3 = df.at[row_idx, col_name3]  # 血压随访分类
        cell_value7 = df.at[row_idx, col_name7]  # 血糖随访分类
        try:
            if not pd.isna(cell_value) and cell_value == xy_and_xt_val:
                cell_value6 = df.at[row_idx, col_name6]  # 出生日期
                ssy_ = 1000
                szy_ = 1000
                # 出生日期不明，不进行处理
                if not pd.isna(cell_value6):
                    birth_year = int(str(cell_value6)[:4])
                    current_year = datetime.now().year
                    age = current_year - birth_year
                    cell_value4 = df.at[row_idx, col_name4]  # 收缩压
                    cell_value5 = df.at[row_idx, col_name5]  # 舒张压
                    ssy_ = float(cell_value4) if not pd.isna(cell_value4) else ssy_
                    szy_ = float(cell_value5) if not pd.isna(cell_value5) else szy_
                    if age < old_age:  # 非老年人
                        if ssy_ < n_old_ssy and szy_ < n_old_szy:
                            if pd.isna(cell_value3) or val not in cell_value3:
                                cell = ws[f'{col_letter1}{excel_row}']
                                cell.fill = fill  # 标色
                                comment = Comment(n_old_com, '')
                                cell.comment = comment
                    else:  # 老年人
                        if ssy_ < y_old_ssy and szy_ < y_old_szy:
                            if pd.isna(cell_value3) or val not in cell_value3:
                                cell = ws[f'{col_letter1}{excel_row}']
                                cell.fill = fill  # 标色
                                comment = Comment(y_old_com, '')
                                cell.comment = comment
        except Exception as e:
            err_info = f"处理单元格 {col_letter1}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)
        try:
            if not pd.isna(cell_value) and cell_value == xy_and_xt_val:
                cell_value8 = df.at[row_idx, col_name8]  # 空腹血糖
                if not pd.isna(cell_value8) and str(cell_value8) != '未查' and float(cell_value8) < xt_max:
                    if pd.isna(cell_value7) or val not in cell_value7:
                        cell = ws[f'{col_letter2}{excel_row}']
                        cell.fill = fill  # 标色
                        comment = Comment(xt_com, '')
                        cell.comment = comment
        except Exception as e:
            err_info = f"处理单元格 {col_letter2}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)


# 体质指数(BMI):
def tzzs(df, ws, col_name1, col_name2, col_name3, error_list):
    """

    :param df: 预处理表格
    :param ws: 待生成表格
    :param col_name1: 体质指数(BMI)
    :param col_name2: 体重
    :param col_name3: 目标体重
    :param error_list: 报错列表
    :return:
    """
    color = str(rules[col_name1]['color'])
    # 设置单元格背景色
    fill = PatternFill(
        start_color=color,
        end_color=color,
        fill_type='solid'
    )
    col_letter = column_map[col_name1]
    max_ = rules[col_name1]['max']
    gap_com = rules[col_name1]['comment1']
    tz_com = rules[col_name1]['comment2']
    for row_idx in range(handle_num):
        excel_row = row_idx + 2  # Excel数据行号（标题在第1行）
        cell_value = df.at[row_idx, col_name1]
        try:
            if pd.isna(cell_value) or str(cell_value) == '未查':
                cell = ws[f'{col_letter}{excel_row}']
                cell.fill = fill  # 标色
                comment = Comment(gap_com, '')
                cell.comment = comment
            elif float(cell_value) > max_:
                cell_value1 = df.at[row_idx, col_name2]
                cell_value2 = df.at[row_idx, col_name3]
                if not pd.isna(cell_value1) and not pd.isna(cell_value2) and str(cell_value1) != '不详' and str(
                        cell_value2) != '不详' and not 1 <= float(cell_value1) - float(
                    cell_value2) <= 2:
                    cell = ws[f'{col_letter}{excel_row}']
                    cell.fill = fill  # 标色
                    comment = Comment(tz_com, '')
                    cell.comment = comment
        except Exception as e:
            err_info = f"处理单元格 {col_letter}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)


# 随访人
def sfr(df, ws, col_name, error_list):
    """

    :param df: 预处理表格
    :param ws: 待生成表格
    :param col_name: 随访人
    :param error_list: 报错列表
    :return:
    """
    color = str(rules[col_name]['color'])
    # 设置单元格背景色
    fill = PatternFill(
        start_color=color,
        end_color=color,
        fill_type='solid'
    )
    col_letter = column_map[col_name]
    com = rules[col_name]['comment']
    for row_idx in range(handle_num):
        excel_row = row_idx + 2
        cell_value = df.at[row_idx, col_name]
        try:
            if pd.isna(cell_value) or str(cell_value) == '未查':
                cell = ws[f'{col_letter}{excel_row}']
                cell.fill = fill  # 标色
                comment = Comment(com, '')
                cell.comment = comment
        except Exception as e:
            err_info = f"处理单元格 {col_letter}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)

# 是否加入国家标准版
def sfjrgjbzb(df, ws, col_name, error_list):
    """
    :param df: 预处理表格
    :param ws: 待生成表格
    :param col_name: 随访人
    :param error_list: 报错列表
    :return:
    """
    # color = str(rules[col_name]['color'])
    # # 设置单元格背景色
    fill = PatternFill(
        start_color="FFFF00",
        end_color="FFFF00",
        fill_type='solid'
    )
    col_letter = column_map[col_name]
    # com = rules[col_name]['comment']
    for row_idx in range(handle_num):
        excel_row = row_idx + 2
        cell_value = df.at[row_idx, col_name]
        try:
            if pd.isna(cell_value):
                cell = ws[f'{col_letter}{excel_row}']
                cell.fill = fill  # 标色
                comment = Comment("单元格为空", '')
                cell.comment = comment
            else:
                if cell_value == "否":
                    cell = ws[f'{col_letter}{excel_row}']
                    cell.fill = fill  # 标色
                    comment = Comment("是否加入国家标准版为否", '')
                    cell.comment = comment
        except Exception as e:
            err_info = f"处理单元格 {col_letter}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)
# 收缩压
def ssy(df, ws, col_name1, col_name2, col_name3, error_list):
    """
    处理收缩压数据，标记异常值和重复值

    :param df: 预处理表格
    :param ws: 待生成表格
    :param col_name1: 收缩压列名
    :param col_name2: 身份证号列名（用于标识同一个人）
    :param col_name3： 随访日期列名
    :param error_list: 报错列表
    :return:
    """
    color = str(rules[col_name1]['color'])
    # 设置单元格背景色
    fill = PatternFill(
        start_color=color,
        end_color=color,
        fill_type='solid'
    )
    col_letter = column_map[col_name1]
    ssy_max = float(rules[col_name1]['max'])
    ssy_min = float(rules[col_name1]['min'])
    gap_com = rules[col_name1]['comment1']  # 空值或"未查"的注释

    # 修改：使用新的重复注释，覆盖原来的"近两次收缩压不应相同"
    dup_com = "收缩压存在重复"  # 替换原有comment3
    ssy_com = rules[col_name1]['comment2']  # 数值范围异常的注释

    # 修改：判断同一个人（相同身份证号）在不同随访日期有相同的收缩压值
    # 先排除空值和"未查"的情况
    valid_mask = ~(pd.isna(df[col_name1]) | (df[col_name1].astype(str) == '未查'))
    # 对有效数据标记重复：同一人且收缩压相同但随访日期不同
    duplicate_mask = df[valid_mask].duplicated(
        subset=[col_name2, col_name1],  # 身份证号和收缩压相同
        keep=False  # 标记所有重复项
    )

    # 将重复标记映射回原始DataFrame
    df['收缩压重复'] = False
    df.loc[valid_mask, '收缩压重复'] = duplicate_mask

    for row_idx in range(handle_num):
        excel_row = row_idx + 2
        cell_value = df.at[row_idx, col_name1]
        try:
            if pd.isna(cell_value) or str(cell_value) == '未查':
                cell = ws[f'{col_letter}{excel_row}']
                cell.fill = fill  # 标色
                comment = Comment(gap_com, '')
                cell.comment = comment
            else:
                cell = ws[f'{col_letter}{excel_row}']
                com_ = ''
                # 修改：使用新的重复判断标志
                dup_flag = df.at[row_idx, '收缩压重复']

                if dup_flag:
                    cell.fill = fill  # 标色
                    com_ += dup_com  # 使用新的重复注释

                # 检查数值是否在正常范围内
                if not ssy_min <= float(cell_value) <= ssy_max:
                    cell.fill = fill  # 标色
                    if com_ != '':
                        com_ += '\n'
                    com_ += ssy_com

                if com_ != '':
                    comment = Comment(com_, '')
                    cell.comment = comment
        except Exception as e:
            err_info = f"处理单元格 {col_letter}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)

    # 清理临时添加的列
    df.drop(['收缩压重复'], axis=1, inplace=True)


# 舒张压
def szy(df, ws, col_name1, col_name2, col_name3, error_list):
    """
    处理舒张压数据，标记异常值和重复值

    :param df: 预处理表格
    :param ws: 待生成表格
    :param col_name1: 舒张压列名
    :param col_name2: 身份证号列名（用于标识同一个人）
    :param col_name3： 随访日期列名
    :param error_list: 报错列表
    :return:
    """
    color = str(rules[col_name1]['color'])
    # 设置单元格背景色
    fill = PatternFill(
        start_color=color,
        end_color=color,
        fill_type='solid'
    )
    col_letter = column_map[col_name1]
    szy_max = float(rules[col_name1]['max'])
    szy_min = float(rules[col_name1]['min'])
    gap_com = rules[col_name1]['comment1']  # 空值或"未查"的注释

    # 修改：使用新的重复注释，覆盖原来的注释
    dup_com = "舒张压存在重复"  # 替换原有comment3
    szy_com = rules[col_name1]['comment2']  # 数值范围异常的注释

    # 修改：判断同一个人（相同身份证号）在不同随访日期有相同的舒张压值
    # 先排除空值和"未查"的情况
    valid_mask = ~(pd.isna(df[col_name1]) | (df[col_name1].astype(str) == '未查'))
    # 对有效数据标记重复：同一人且舒张压相同但随访日期不同
    duplicate_mask = df[valid_mask].duplicated(
        subset=[col_name2, col_name1],  # 身份证号和舒张压相同
        keep=False  # 标记所有重复项
    )

    # 将重复标记映射回原始DataFrame
    df['舒张压重复'] = False
    df.loc[valid_mask, '舒张压重复'] = duplicate_mask

    for row_idx in range(handle_num):
        excel_row = row_idx + 2
        cell_value = df.at[row_idx, col_name1]
        try:
            if pd.isna(cell_value) or str(cell_value) == '未查':
                cell = ws[f'{col_letter}{excel_row}']
                cell.fill = fill  # 标色
                comment = Comment(gap_com, '')
                cell.comment = comment
            else:
                cell = ws[f'{col_letter}{excel_row}']
                com_ = ''
                # 修改：使用新的重复判断标志
                dup_flag = df.at[row_idx, '舒张压重复']

                if dup_flag:
                    cell.fill = fill  # 标色
                    com_ += dup_com  # 使用新的重复注释

                # 检查数值是否在正常范围内
                if not szy_min <= float(cell_value) <= szy_max:
                    cell.fill = fill  # 标色
                    if com_ != '':
                        com_ += '\n'
                    com_ += szy_com

                if com_ != '':
                    comment = Comment(com_, '')
                    cell.comment = comment
        except Exception as e:
            err_info = f"处理单元格 {col_letter}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)

    # 清理临时添加的列
    df.drop(['舒张压重复'], axis=1, inplace=True)


def tz(df, ws, col_name1, col_name2, col_name3, error_list):
    """
    处理体重数据，包括范围校验、重复体重校验和相邻体重差值校验

    :param df: 预处理表格
    :param ws: 待生成表格
    :param col_name1: 体重列名
    :param col_name2: 身份证号列名
    :param col_name3： 随访日期列名
    :param error_list: 报错列表
    :return:
    """
    # 获取规则中定义的颜色和参数
    color = str(rules[col_name1]['color'])
    # 定义相邻体重超过5kg的标记颜色（红色）
    red_color = "FF0000"  # 红色

    # 设置单元格背景色
    fill = PatternFill(
        start_color=color,
        end_color=color,
        fill_type='solid'
    )

    # 创建红色背景填充样式
    red_fill = PatternFill(
        start_color=red_color,
        end_color=red_color,
        fill_type='solid'
    )

    col_letter = column_map[col_name1]
    tz_max = float(rules[col_name1]['max'])
    tz_min = float(rules[col_name1]['min'])
    gap_com = rules[col_name1]['comment1']
    tz_com = rules[col_name1]['comment2']
    # 重复体重注释
    dup_com = "体重存在重复"
    # 相邻体重超过5kg的注释
    adjacent_com = "相邻体重超过5kg"

    # 关键修改：检查同一人不同日期的体重是否存在重复
    # 按身份证号和体重分组，然后标记每组中日期不唯一的记录
    df['体重存在重复'] = False
    for (id_, weight), group in df.groupby([col_name2, col_name1]):
        # 检查该组中是否有多个不同的随访日期
        if group[col_name3].nunique() > 1:
            # 标记该组中的所有记录为存在重复
            df.loc[group.index, '体重存在重复'] = True

    # 处理相邻体重差值超过5kg的情况
    # 1. 确保体重列是数值类型
    df[col_name1] = pd.to_numeric(df[col_name1], errors='coerce')

    # 2. 按身份证号分组，计算每个个体相邻记录的体重差值
    # 创建临时列用于标记相邻体重超标
    df['相邻体重超标'] = False
    # 创建临时列存储相邻行的索引
    df['相邻行索引'] = -1

    # 按身份证号分组处理
    for id_, group in df.groupby(col_name2):
        # 按随访日期排序
        sorted_group = group.sort_values(by=col_name3)
        # 计算相邻体重差值（绝对值）
        weight_diff = sorted_group[col_name1].diff().abs()
        # 标记差值超过5kg的记录对
        mask = weight_diff > 5

        # 记录相邻超标行的索引关系
        for i in range(1, len(mask)):
            if mask.iloc[i]:
                current_idx = sorted_group.index[i]
                prev_idx = sorted_group.index[i - 1]
                df.at[current_idx, '相邻体重超标'] = True
                df.at[current_idx, '相邻行索引'] = prev_idx
                df.at[prev_idx, '相邻体重超标'] = True
                df.at[prev_idx, '相邻行索引'] = current_idx

    # 先预检查每行是否有其他注释
    df['有其他注释'] = False
    for row_idx in range(len(df)):
        cell_value = df.at[row_idx, col_name1]
        try:
            if not (pd.isna(cell_value) or str(cell_value) == '未查'):
                com_ = ''
                # 检查体重是否存在重复
                if df.at[row_idx, '体重存在重复']:
                    com_ += dup_com

                # 检查体重范围
                if not tz_min <= float(cell_value) <= tz_max:
                    if com_ != '':
                        com_ += '\n'
                    com_ += tz_com

                # 如果有其他注释，标记
                if com_ != '':
                    df.at[row_idx, '有其他注释'] = True
        except:
            continue

    # 处理每个单元格
    for row_idx in range(len(df)):
        excel_row = row_idx + 2
        cell_value = df.at[row_idx, col_name1]
        try:
            if pd.isna(cell_value) or str(cell_value) == '未查':
                # 空值或未查情况
                cell = ws[f'{col_letter}{excel_row}']
                cell.fill = fill  # 标色
                comment = Comment(gap_com, '')
                cell.comment = comment
            else:
                cell = ws[f'{col_letter}{excel_row}']
                com_ = ''
                # 检查体重是否存在重复
                if df.at[row_idx, '体重存在重复']:
                    cell.fill = fill  # 标色
                    com_ += dup_com

                # 检查体重范围
                if not tz_min <= float(cell_value) <= tz_max:
                    cell.fill = fill  # 标色
                    if com_ != '':
                        com_ += '\n'
                    com_ += tz_com

                # 检查相邻体重是否超过5kg
                adjacent_flag = df.at[row_idx, '相邻体重超标']
                if adjacent_flag:
                    # 获取相邻行索引
                    adjacent_row_idx = df.at[row_idx, '相邻行索引']

                    # 检查当前行和相邻行的注释情况
                    current_has_comment = df.at[row_idx, '有其他注释']
                    adjacent_has_comment = df.at[adjacent_row_idx, '有其他注释'] if adjacent_row_idx != -1 else False

                    # 如果当前行没有其他注释，但相邻行有，则当前行添加相邻体重注释并标红
                    if not current_has_comment and adjacent_has_comment:
                        cell.fill = red_fill  # 标红当前单元格
                        if com_ != '':
                            com_ += '\n'
                        com_ += adjacent_com
                    # 如果双方都没有其他注释，当前行添加注释并标红
                    elif not current_has_comment and not adjacent_has_comment:
                        cell.fill = red_fill
                        if com_ != '':
                            com_ += '\n'
                        com_ += adjacent_com
                    # 如果当前行有其他注释，则不添加相邻体重注释（保持原有注释）

                # 添加注释
                if com_ != '':
                    comment = Comment(com_, '')
                    cell.comment = comment
        except Exception as e:
            err_info = f"处理单元格 {col_letter}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)

    # 清理临时列
    df.drop(['体重存在重复', '相邻体重超标', '相邻行索引', '有其他注释'],
            axis=1, inplace=True)


# 身高
def sg(df, ws, col_name, error_list):
    """

    :param df: 预处理表格
    :param ws: 待生成表格
    :param col_name: 身高
    :param error_list: 报错列表
    :return:
    """
    color = str(rules[col_name]['color'])
    # 设置单元格背景色
    fill = PatternFill(
        start_color=color,
        end_color=color,
        fill_type='solid'
    )
    col_letter = column_map[col_name]
    sg_max = float(rules[col_name]['max'])
    sg_min = float(rules[col_name]['min'])
    gap_com = rules[col_name]['comment1']
    sg_com = rules[col_name]['comment2']
    for row_idx in range(handle_num):
        excel_row = row_idx + 2
        cell_value = df.at[row_idx, col_name]
        try:
            if pd.isna(cell_value) or str(cell_value) == '未查':
                cell = ws[f'{col_letter}{excel_row}']
                cell.fill = fill  # 标色
                comment = Comment(gap_com, '')
                cell.comment = comment
            elif not sg_min <= float(cell_value) <= sg_max:
                cell = ws[f'{col_letter}{excel_row}']
                cell.fill = fill  # 标色
                comment = Comment(sg_com, '')
                cell.comment = comment
        except Exception as e:
            err_info = f"处理单元格 {col_letter}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)


# 心率
def xl(df, ws, col_name1, col_name2, col_name3, error_list):
    """
    处理心率数据，标记异常值和重复值

    :param df: 预处理表格
    :param ws: 待生成表格
    :param col_name1: 心率列名
    :param col_name2: 身份证号列名（用于标识同一个人）
    :param col_name3： 随访日期列名
    :param error_list: 报错列表
    :return:
    """
    color = str(rules[col_name1]['color'])
    # 设置单元格背景色
    fill = PatternFill(
        start_color=color,
        end_color=color,
        fill_type='solid'
    )
    col_letter = column_map[col_name1]
    xl_max = float(rules[col_name1]['max'])
    xl_min = float(rules[col_name1]['min'])
    gap_com = rules[col_name1]['comment1']  # 空值或"未查"的注释

    # 修改：使用新的重复注释，覆盖原来的注释
    dup_com = "心率存在重复"  # 替换原有comment3
    xl_com = rules[col_name1]['comment2']  # 数值范围异常的注释

    # 修改：判断同一个人（相同身份证号）在不同随访日期有相同的心率值
    # 先排除空值和"未查"的情况
    valid_mask = ~(pd.isna(df[col_name1]) | (df[col_name1].astype(str) == '未查'))
    # 对有效数据标记重复：同一人且心率相同但随访日期不同
    duplicate_mask = df[valid_mask].duplicated(
        subset=[col_name2, col_name1],  # 身份证号和心率相同
        keep=False  # 标记所有重复项
    )

    # 将重复标记映射回原始DataFrame
    df['心率重复'] = False
    df.loc[valid_mask, '心率重复'] = duplicate_mask

    for row_idx in range(handle_num):
        excel_row = row_idx + 2
        cell_value = df.at[row_idx, col_name1]
        try:
            if pd.isna(cell_value) or str(cell_value) == '未查':
                cell = ws[f'{col_letter}{excel_row}']
                cell.fill = fill  # 标色
                comment = Comment(gap_com, '')
                cell.comment = comment
            else:
                cell = ws[f'{col_letter}{excel_row}']
                com_ = ''
                # 修改：使用新的重复判断标志
                dup_flag = df.at[row_idx, '心率重复']

                if dup_flag:
                    cell.fill = fill  # 标色
                    com_ += dup_com  # 使用新的重复注释

                # 检查数值是否在正常范围内
                if not xl_min <= float(cell_value) <= xl_max:
                    cell.fill = fill  # 标色
                    if com_ != '':
                        com_ += '\n'
                    com_ += xl_com

                if com_ != '':
                    comment = Comment(com_, '')
                    cell.comment = comment
        except Exception as e:
            err_info = f"处理单元格 {col_letter}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)

    # 清理临时添加的列
    df.drop(['心率重复'], axis=1, inplace=True)


# 主食量
def zsl(df, ws, col_name1, col_name2, error_list):
    """

    :param df: 预处理表格
    :param ws: 待生成表格
    :param col_name1: 主食量
    :param col_name2: 目标主食量
    :param error_list: 报错列表
    :return:
    """
    color = str(rules[col_name1]['color'])
    # 设置单元格背景色
    fill = PatternFill(
        start_color=color,
        end_color=color,
        fill_type='solid'
    )
    col_letter = column_map[col_name1]
    col_letter1 = column_map[col_name2]
    zzl_max = float(rules[col_name1]['max'])
    zzl_min = float(rules[col_name1]['min'])
    gap_com = rules[col_name1]['comment1']
    zzl_com = rules[col_name1]['comment2']
    for row_idx in range(handle_num):
        excel_row = row_idx + 2
        cell_value = df.at[row_idx, col_name1]  # 主食量
        try:
            if pd.isna(cell_value) or str(cell_value) == '未查':
                cell = ws[f'{col_letter}{excel_row}']
                cell.fill = fill  # 标色
                comment = Comment(gap_com, '')
                cell.comment = comment
            elif not zzl_min <= float(cell_value) <= zzl_max:
                cell = ws[f'{col_letter}{excel_row}']
                cell.fill = fill  # 标色
                comment = Comment(zzl_com, '')
                cell.comment = comment
        except Exception as e:
            err_info = f"处理单元格 {col_letter}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)
        cell_value1 = df.at[row_idx, col_name2]  # 目标主食量
        try:
            if pd.isna(cell_value1) or str(cell_value1) == '未查':
                pass
                # cell = ws[f'{col_letter1}{excel_row}']
                # cell.fill = fill  # 标色
                # comment = Comment(gap_com, '')
                # cell.comment = comment
            elif not zzl_min <= float(cell_value1) <= zzl_max:
                cell = ws[f'{col_letter1}{excel_row}']
                cell.fill = fill  # 标色
                comment = Comment(zzl_com, '')
                cell.comment = comment
        except Exception as e:
            err_info = f"处理单元格 {col_letter1}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)


# 随访建议
def sfjy(df, ws, col_name1, col_name2, col_name3, col_name4, col_name5, error_list):
    """

    :param df: 预处理表格
    :param ws: 待生成表格
    :param col_name1: 身份证号
    :param col_name2: 随访日期
    :param col_name3: 评估
    :param col_name4: 生活方式
    :param col_name5: 医生建议
    :param error_list: 报错列表
    :return:
    """
    color = str(rules['随访建议']['color'])
    # 设置单元格背景色
    fill = PatternFill(
        start_color=color,
        end_color=color,
        fill_type='solid'
    )
    col_letter3 = column_map[col_name3]
    col_letter4 = column_map[col_name4]
    col_letter5 = column_map[col_name5]
    gap_com = rules['随访建议']['comment1']
    sf_com = rules['随访建议']['comment2']
    df['随访季度'] = df[col_name2].apply(lambda x: get_quarter(parse_date(x)))
    duplicate_mask = df.duplicated(subset=[col_name1, '随访季度', col_name3], keep=False)
    df['评估重复'] = duplicate_mask
    duplicate_mask = df.duplicated(subset=[col_name1, '随访季度', col_name4], keep=False)
    df['生活方式重复'] = duplicate_mask
    duplicate_mask = df.duplicated(subset=[col_name1, '随访季度', col_name5], keep=False)
    df['医生建议重复'] = duplicate_mask
    for row_idx in range(handle_num):
        excel_row = row_idx + 2
        cell_value1 = df.at[row_idx, col_name3]  # 评估
        cell_value2 = df.at[row_idx, col_name4]  # 生活方式
        cell_value3 = df.at[row_idx, col_name5]  # 医生建议
        try:  # 评估暂不处理
            pass
            # cell = ws[f'{col_letter3}{excel_row}']
            # if pd.isna(cell_value1):
            #     cell.fill = fill
            #     comment = Comment(gap_com, '')
            #     cell.comment = comment
            # else:
            #     if df.at[row_idx, '评估重复']:
            #         cell.fill = fill
            #         comment = Comment(sf_com, '')
            #         cell.comment = comment
        except Exception as e:
            err_info = f"处理单元格 {col_letter3}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)
        try:
            cell = ws[f'{col_letter4}{excel_row}']
            if pd.isna(cell_value2):
                cell.fill = fill
                comment = Comment(gap_com, '')
                cell.comment = comment
            else:
                if df.at[row_idx, '生活方式重复']:
                    cell.fill = fill
                    comment = Comment(sf_com, '')
                    cell.comment = comment
        except Exception as e:
            err_info = f"处理单元格 {col_letter4}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)
        try:
            cell = ws[f'{col_letter5}{excel_row}']
            if pd.isna(cell_value3):
                cell.fill = fill
                comment = Comment(gap_com, '')
                cell.comment = comment
            else:
                if df.at[row_idx, '医生建议重复']:
                    cell.fill = fill
                    comment = Comment(sf_com, '')
                    cell.comment = comment
        except Exception as e:
            err_info = f"处理单元格 {col_letter5}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)
    # 处理完后删除新增判断项
    df.drop(['随访季度', '评估重复', '生活方式重复', '医生建议重复'], axis=1, inplace=True)


# 吸烟
def xy(df, ws, col_name1, col_name2, error_list):
    """

    :param df: 预处理表格
    :param ws: 待生成表格
    :param col_name1: 日吸烟量
    :param col_name2: 目标日吸烟量
    :param error_list: 报错列表
    :return:
    """
    color = str(rules[col_name1]['color'])
    # 设置单元格背景色
    fill = PatternFill(
        start_color=color,
        end_color=color,
        fill_type='solid'
    )
    col_letter = column_map[col_name1]
    col_letter1 = column_map[col_name2]
    xy_max = float(rules[col_name1]['max'])
    xy_max_com = rules[col_name1]['comment1']
    xy_over_com = rules[col_name1]['comment2']
    for row_idx in range(handle_num):
        excel_row = row_idx + 2
        cell_value = df.at[row_idx, col_name1]
        xyl = -1
        try:
            if pd.isna(cell_value) or str(cell_value) == '未查':
                pass
                # cell = ws[f'{col_letter}{excel_row}']
                # cell.fill = fill  # 标色
                # comment = Comment(gap_com, '')
                # cell.comment = comment
            else:
                xyl = float(cell_value)
                if xyl > xy_max:
                    cell = ws[f'{col_letter}{excel_row}']
                    cell.fill = fill  # 标色
                    comment = Comment(xy_max_com, '')
                    cell.comment = comment
        except Exception as e:
            err_info = f"处理单元格 {col_letter}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)
        cell_value1 = df.at[row_idx, col_name2]
        try:
            if pd.isna(cell_value1) or str(cell_value1) == '未查':
                pass
                # cell = ws[f'{col_letter1}{excel_row}']
                # cell.fill = fill  # 标色
                # comment = Comment(gap_com, '')
                # cell.comment = comment
            else:
                com_ = ''
                cell = ws[f'{col_letter1}{excel_row}']
                if float(cell_value1) > xy_max:
                    cell.fill = fill  # 标色
                    com_ += xy_max_com
                if not pd.isna(cell_value) and str(cell_value) != '未查' and float(cell_value1) > xyl:
                    cell.fill = fill  # 标色
                    if com_ != '':
                        com_ += '\n'
                    com_ += xy_over_com
                if com_ != '':
                    comment = Comment(com_, '')
                    cell.comment = comment
        except Exception as e:
            err_info = f"处理单元格 {col_letter1}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)


# 饮酒
def yj(df, ws, col_name1, col_name2, error_list):
    """

    :param df: 预处理表格
    :param ws: 待生成表格
    :param col_name1: 日饮酒量
    :param col_name2: 目标日饮酒量
    :param error_list: 报错列表
    :return:
    """
    color = str(rules[col_name1]['color'])
    # 设置单元格背景色
    fill = PatternFill(
        start_color=color,
        end_color=color,
        fill_type='solid'
    )
    col_letter = column_map[col_name1]
    col_letter1 = column_map[col_name2]
    yj_max = float(rules[col_name1]['max'])
    yj_max_com = rules[col_name1]['comment1']
    yj_over_com = rules[col_name1]['comment2']
    for row_idx in range(handle_num):
        excel_row = row_idx + 2
        cell_value = df.at[row_idx, col_name1]
        yjl = -1
        try:
            if pd.isna(cell_value) or str(cell_value) == '未查':
                pass
                # cell = ws[f'{col_letter}{excel_row}']
                # cell.fill = fill  # 标色
                # comment = Comment(gap_com, '')
                # cell.comment = comment
            else:
                yjl = float(cell_value)
                if yjl > yj_max:
                    cell = ws[f'{col_letter}{excel_row}']
                    cell.fill = fill  # 标色
                    comment = Comment(yj_max_com, '')
                    cell.comment = comment
        except Exception as e:
            err_info = f"处理单元格 {col_letter}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)
        cell_value1 = df.at[row_idx, col_name2]
        try:
            if pd.isna(cell_value1) or str(cell_value1) == '未查':
                pass
                # cell = ws[f'{col_letter1}{excel_row}']
                # cell.fill = fill  # 标色
                # comment = Comment(gap_com, '')
                # cell.comment = comment
            else:
                com_ = ''
                cell = ws[f'{col_letter1}{excel_row}']
                if float(cell_value1) > yj_max:
                    cell.fill = fill  # 标色
                    com_ += yj_max_com
                if not pd.isna(cell_value) and str(cell_value) != '未查' and float(cell_value1) > yjl:
                    cell.fill = fill  # 标色
                    if com_ != '':
                        com_ += '\n'
                    com_ += yj_over_com
                if com_ != '':
                    comment = Comment(com_, '')
                    cell.comment = comment
        except Exception as e:
            err_info = f"处理单元格 {col_letter1}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)


# 运动次数
def ydcs(df, ws, col_name1, col_name2, error_list):
    """

    :param df: 预处理表格
    :param ws: 待生成表格
    :param col_name1: 运动次数
    :param col_name2: 目标运动次数
    :param error_list: 报错列表
    :return:
    """
    color = str(rules[col_name1]['color'])
    # 设置单元格背景色
    fill = PatternFill(
        start_color=color,
        end_color=color,
        fill_type='solid'
    )
    col_letter = column_map[col_name1]
    col_letter1 = column_map[col_name2]
    ydcs_max = float(rules[col_name1]['max'])
    ydcs_max_com = rules[col_name1]['comment1']
    for row_idx in range(handle_num):
        excel_row = row_idx + 2
        cell_value = df.at[row_idx, col_name1]
        try:
            if pd.isna(cell_value) or str(cell_value) == '未查':
                pass
                # cell = ws[f'{col_letter}{excel_row}']
                # cell.fill = fill  # 标色
                # comment = Comment(gap_com, '')
                # cell.comment = comment
            else:
                if float(cell_value) > ydcs_max:
                    cell = ws[f'{col_letter}{excel_row}']
                    cell.fill = fill  # 标色
                    comment = Comment(ydcs_max_com, '')
                    cell.comment = comment
        except Exception as e:
            err_info = f"处理单元格 {col_letter}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)
        cell_value1 = df.at[row_idx, col_name2]
        try:
            if pd.isna(cell_value1) or str(cell_value1) == '未查':
                pass
                # cell = ws[f'{col_letter1}{excel_row}']
                # cell.fill = fill  # 标色
                # comment = Comment(gap_com, '')
                # cell.comment = comment
            else:
                if float(cell_value1) > ydcs_max:
                    cell = ws[f'{col_letter1}{excel_row}']
                    cell.fill = fill  # 标色
                    comment = Comment(ydcs_max_com, '')
                    cell.comment = comment
        except Exception as e:
            err_info = f"处理单元格 {col_letter1}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)


# 运动时间
def ydsj(df, ws, col_name1, col_name2, error_list):
    """

    :param df: 预处理表格
    :param ws: 待生成表格
    :param col_name1: 运动时间
    :param col_name2: 目标运动时间
    :param error_list: 报错列表
    :return:
    """
    color = str(rules[col_name1]['color'])
    # 设置单元格背景色
    fill = PatternFill(
        start_color=color,
        end_color=color,
        fill_type='solid'
    )
    col_letter = column_map[col_name1]
    col_letter1 = column_map[col_name2]
    ydsj_max = float(rules[col_name1]['max'])
    ydsj_max_com = rules[col_name1]['comment1']
    for row_idx in range(handle_num):
        excel_row = row_idx + 2
        cell_value = df.at[row_idx, col_name1]
        try:
            if pd.isna(cell_value) or str(cell_value) == '未查':
                pass
                # cell = ws[f'{col_letter}{excel_row}']
                # cell.fill = fill  # 标色
                # comment = Comment(gap_com, '')
                # cell.comment = comment
            else:
                if float(cell_value) > ydsj_max:
                    cell = ws[f'{col_letter}{excel_row}']
                    cell.fill = fill  # 标色
                    comment = Comment(ydsj_max_com, '')
                    cell.comment = comment
        except Exception as e:
            err_info = f"处理单元格 {col_letter}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)
        cell_value1 = df.at[row_idx, col_name2]
        try:
            if pd.isna(cell_value1) or str(cell_value1) == '未查':
                pass
                # cell = ws[f'{col_letter1}{excel_row}']
                # cell.fill = fill  # 标色
                # comment = Comment(gap_com, '')
                # cell.comment = comment
            else:
                if float(cell_value1) > ydsj_max:
                    cell = ws[f'{col_letter1}{excel_row}']
                    cell.fill = fill  # 标色
                    comment = Comment(ydsj_max_com, '')
                    cell.comment = comment
        except Exception as e:
            err_info = f"处理单元格 {col_letter1}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)


# 症状编辑框
def zzbjk(df, ws, col_name1, col_name2, col_name3, error_list):
    """

    :param df: 预处理表格
    :param ws: 待生成表格
    :param col_name1: 身份证号
    :param col_name2: 随访日期
    :param col_name3: 症状编辑框
    :param error_list: 报错列表
    :return:
    """
    color = str(rules[col_name3]['color'])
    # 设置单元格背景色
    fill = PatternFill(
        start_color=color,
        end_color=color,
        fill_type='solid'
    )
    col_letter = column_map[col_name3]
    gap_com = rules[col_name3]['comment1']
    zz_com = rules[col_name3]['comment2']
    df['随访季度'] = df[col_name2].apply(lambda x: get_quarter(parse_date(x)))
    duplicate_mask = df.duplicated(subset=[col_name1, '随访季度', col_name3], keep=False)
    df['症状编辑框重复'] = duplicate_mask
    for row_idx in range(handle_num):
        excel_row = row_idx + 2
        cell_value = df.at[row_idx, col_name3]  # 评估
        try:
            cell = ws[f'{col_letter}{excel_row}']
            if pd.isna(cell_value) or cell_value == '未查':
                pass
                # cell.fill = fill
                # comment = Comment(gap_com, '')
                # cell.comment = comment
            else:
                if df.at[row_idx, '症状编辑框重复']:
                    cell.fill = fill
                    comment = Comment(zz_com, '')
                    cell.comment = comment
        except Exception as e:
            err_info = f"处理单元格 {col_letter}{excel_row} 时出错: {str(e)}"
            print(err_info)
            error_list.append(err_info)
    # 处理完后删除新增判断项
    df.drop(['随访季度', '症状编辑框重复'], axis=1, inplace=True)
